select left(o.name,47) name, convert(varchar(12),databasepropertyex(d.name,'recovery')) as [recovery]
, convert(varchar(28),suser_sname(sid)) as [owner]	--, crdate as created
, cast([last full backup] as varchar(20)) as [last full backup],  isnull(datediff(dd,[last full backup],getdate()),99999) as [days old]
from (
select d.name, max(backup_start_date) as [last full backup]	
from master..sysdatabases d left outer join msdb..backupset s on s.database_name = d.name join msdb..backupmediafamily fam on fam.media_set_id = s.media_set_id 
where s.type = 'D' and backup_finish_date is not null --and datediff(dd,backup_start_date,getdate())
and d.name <> 'tempdb' and databasepropertyex(d.name,'IsInStandby')=0 and status&1048!=1048
group by d.name, database_name
--
union all
--
select d.name , NULL as [last full backup]
from master..sysdatabases d 
where not exists ( select database_name as name
, backup_start_date , backup_finish_date as finished
from msdb..backupset s join msdb..backupmediafamily fam on fam.media_set_id = s.media_set_id
where d.name = database_name and type = 'D' and backup_finish_date is not null )
and d.name <> 'tempdb' and databasepropertyex(d.name,'IsInStandby')=0 and status&1048!=1048
) o join master..sysdatabases d on o.name = d.name
order by 1,5 ;
